from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, func, extract
from typing import Optional, List, Tuple
from datetime import datetime
import math

from models.family_tree import FamilyTreeModel, FamilyRelationshipModel
from schemas.family_tree_schema import (
    FamilyTreeCreate, FamilyTreeUpdate, FamilyTreeQuery,
    FamilyRelationshipCreate, FamilyRelationshipQuery,
    NearbyGraveQuery
)


def determine_relationship_info(person_role: str, related_person_role: str) -> dict:
    """
    根据角色自动推导关系类型和相互角色
    
    Args:
        person_role: 人员角色
        related_person_role: 关系人角色
    
    Returns:
        dict: 包含 relationship_type 的字典
    """
    # 定义角色映射
    role_mappings = {
        # 父子关系
        ('father', 'son'): 'parent_child',
        ('father', 'daughter'): 'parent_child',
        ('mother', 'son'): 'parent_child',
        ('mother', 'daughter'): 'parent_child',
        ('son', 'father'): 'parent_child',
        ('daughter', 'father'): 'parent_child',
        ('son', 'mother'): 'parent_child',
        ('daughter', 'mother'): 'parent_child',
        
        # 夫妻关系
        ('spouse', 'spouse'): 'spouse',
        
        # 兄弟姐妹关系
        ('brother', 'brother'): 'sibling',
        ('brother', 'sister'): 'sibling',
        ('sister', 'brother'): 'sibling',
        ('sister', 'sister'): 'sibling',
        
        # 精细化的兄弟姐妹关系
        ('elder_brother', 'younger_brother'): 'sibling',
        ('elder_brother', 'younger_sister'): 'sibling',
        ('elder_sister', 'younger_brother'): 'sibling',
        ('elder_sister', 'younger_sister'): 'sibling',
        ('younger_brother', 'elder_brother'): 'sibling',
        ('younger_brother', 'elder_sister'): 'sibling',
        ('younger_sister', 'elder_brother'): 'sibling',
        ('younger_sister', 'elder_sister'): 'sibling',
        ('gege', 'didi'): 'sibling',
        ('jiejie', 'meimei'): 'sibling',
        ('gege', 'meimei'): 'sibling',
        ('jiejie', 'didi'): 'sibling',
        ('didi', 'jiejie'): 'sibling',
        ('meimei', 'jiejie'): 'sibling',
        ('didi', 'gege'): 'sibling',
        ('meimei', 'gege'): 'sibling',
        
        # 兼容传统和新角色的组合
        ('brother', 'elder_brother'): 'sibling',
        ('brother', 'younger_brother'): 'sibling',
        ('brother', 'elder_sister'): 'sibling',
        ('brother', 'younger_sister'): 'sibling',
        ('sister', 'elder_brother'): 'sibling',
        ('sister', 'younger_brother'): 'sibling',
        ('sister', 'elder_sister'): 'sibling',
        ('sister', 'younger_sister'): 'sibling',
        ('elder_brother', 'brother'): 'sibling',
        ('younger_brother', 'brother'): 'sibling',
        ('elder_sister', 'brother'): 'sibling',
        ('younger_sister', 'brother'): 'sibling',
        ('elder_brother', 'sister'): 'sibling',
        ('younger_brother', 'sister'): 'sibling',
        ('elder_sister', 'sister'): 'sibling',
        ('younger_sister', 'sister'): 'sibling',
        
        # 祖孙关系
        ('grandfather', 'grandson'): 'grandparent_grandchild',
        ('grandfather', 'granddaughter'): 'grandparent_grandchild',
        ('grandmother', 'grandson'): 'grandparent_grandchild',
        ('grandmother', 'granddaughter'): 'grandparent_grandchild',
        ('grandson', 'grandfather'): 'grandparent_grandchild',
        ('granddaughter', 'grandfather'): 'grandparent_grandchild',
        ('grandson', 'grandmother'): 'grandparent_grandchild',
        ('granddaughter', 'grandmother'): 'grandparent_grandchild',
        ('grandson', 'grandmother'): 'grandparent_grandchild',

        # 曾祖孙关系
        ('zengzufu', 'zengsun'): 'grandparent_grandchild_zen',
        ('zengzufu', 'zengsunnv'): 'grandparent_grandchild_zen',
        ('zengzumu', 'zengsun'): 'grandparent_grandchild_zen',
        ('zengzumu', 'zengsunnv'): 'grandparent_grandchild_zen',
        ('zengsun', 'zengzufu'): 'grandparent_grandchild_zen',
        ('zengsunnv', 'zengzufu'): 'grandparent_grandchild_zen',
        ('zengsun', 'zengzumu'): 'grandparent_grandchild_zen',
        ('zengsunnv', 'zengzumu'): 'grandparent_grandchild_zen',
    }
    
    relationship_type = role_mappings.get((person_role, related_person_role))
    if not relationship_type:
        raise ValueError(f"不支持的角色组合: {person_role} - {related_person_role}")
    
    return {
        'relationship_type': relationship_type
    }


def auto_determine_sibling_role(db: Session, person_id: str, related_person_id: str, 
                                person_gender: int, related_person_gender: int) -> tuple:
    """
    根据出生日期自动判断兄弟姐妹关系
    
    Args:
        db: 数据库会话
        person_id: 人员ID
        related_person_id: 关系人 ID
        person_gender: 人员性别 (1-男 2-女)
        related_person_gender: 关系人性别 (1-男 2-女)
    
    Returns:
        tuple: (person_role, related_person_role)
    """
    # 查询两人的出生日期
    person = db.query(FamilyTreeModel).filter(
        and_(FamilyTreeModel.id == person_id, FamilyTreeModel.status == 1)
    ).first()
    
    related_person = db.query(FamilyTreeModel).filter(
        and_(FamilyTreeModel.id == related_person_id, FamilyTreeModel.status == 1)
    ).first()
    
    if not person or not related_person or not person.birth_date or not related_person.birth_date:
        # 如果没有出生日期信息，使用传统角色
        person_role = 'brother' if person_gender == 1 else 'sister'
        related_person_role = 'brother' if related_person_gender == 1 else 'sister'
        return person_role, related_person_role
    
    # 根据出生日期判断年龄大小
    person_is_older = person.birth_date < related_person.birth_date
    
    if person_is_older:
        # person 更大
        if person_gender == 1:  # 男性
            person_role = 'elder_brother'
        else:  # 女性
            person_role = 'elder_sister'
            
        if related_person_gender == 1:  # 男性
            related_person_role = 'younger_brother'
        else:  # 女性
            related_person_role = 'younger_sister'
    else:
        # related_person 更大
        if person_gender == 1:  # 男性
            person_role = 'younger_brother'
        else:  # 女性
            person_role = 'younger_sister'
            
        if related_person_gender == 1:  # 男性
            related_person_role = 'elder_brother'
        else:  # 女性
            related_person_role = 'elder_sister'
    
    return person_role, related_person_role


def check_relationship_consistency(person_role: str, related_person_role: str) -> bool:
    """
    检查角色是否一致
    
    Args:
        person_role: 人员角色
        related_person_role: 关系人角色
    
    Returns:
        bool: True 表示一致，False 表示不一致
    """
    try:
        determine_relationship_info(person_role, related_person_role)
        return True
    except ValueError:
        return False


def family_tree_model_to_dict(db_member: FamilyTreeModel) -> dict:
    """将FamilyTreeModel对象转换为dict以支持JSON序列化"""
    return {
        'id': db_member.id,
        'name': db_member.name,
        'taboo_name': db_member.taboo_name,
        'ranking_character': db_member.ranking_character,
        'birth_date': db_member.birth_date,
        'gender': db_member.gender,
        'avatar': db_member.avatar,
        'generation': db_member.generation,
        'biography': db_member.biography,
        'death_date': db_member.death_date,
        'longitude': db_member.longitude,
        'latitude': db_member.latitude,
        'grave_address': db_member.grave_address,
        'grave_images': db_member.grave_images,
        'images': db_member.images,
        'shop_id': db_member.shop_id,
        'tenant_id': db_member.tenant_id,
        'create_by': db_member.create_by,
        'update_by': db_member.update_by,
        'create_time': db_member.create_time,
        'update_time': db_member.update_time,
        'status': db_member.status,
        'deleted_at': db_member.deleted_at
    }


def family_tree_model_to_dict_clean(db_member: FamilyTreeModel) -> dict:
    """将FamilyTreeModel对象转换为清洗后的dict（仅保留指定字段）"""
    return {
        'id': db_member.id,
        'name': db_member.name,
        'taboo_name': db_member.taboo_name,
        'ranking_character': db_member.ranking_character,
        'generation': db_member.generation,
        'shop_id': db_member.shop_id,
        'create_time': db_member.create_time,
        'status': db_member.status,
        'create_by': db_member.create_by,
        'death_date': db_member.death_date,
        'update_by': db_member.update_by
    }


def family_relationship_model_to_dict(db_relationship: FamilyRelationshipModel) -> dict:
    """将FamilyRelationshipModel对象转换为dict以支持JSON序列化"""
    # 安全地获取多租户字段
    shop_id = getattr(db_relationship, 'shop_id', None)
    tenant_id = getattr(db_relationship, 'tenant_id', None)
    
    return {
        'id': db_relationship.id,
        'person_id': db_relationship.person_id,
        'related_person_id': db_relationship.related_person_id,
        'relationship_type': db_relationship.relationship_type,
        'person_role': db_relationship.person_role,
        'related_person_role': db_relationship.related_person_role,
        'shop_id': shop_id,
        'tenant_id': tenant_id,
        'create_by': db_relationship.create_by,
        'create_time': db_relationship.create_time,
        'update_time': db_relationship.update_time,
        'status': db_relationship.status
    }


def family_relationship_model_to_dict_with_names(db_relationship: FamilyRelationshipModel, 
                                               person_name: str = None, 
                                               related_person_name: str = None) -> dict:
    """将FamilyRelationshipModel对象转换为dict，包含人员名称信息
    优先使用缓存的姓名字段（如果存在），否则使用传入的姓名参数
    """
    # 安全地获取缓存的姓名字段（检查字段是否存在）
    cached_person_name = getattr(db_relationship, 'person_name', None)
    cached_related_person_name = getattr(db_relationship, 'related_person_name', None)
    
    final_person_name = cached_person_name or person_name
    final_related_person_name = cached_related_person_name or related_person_name
    
    # 安全地获取多租户字段
    shop_id = getattr(db_relationship, 'shop_id', None)
    tenant_id = getattr(db_relationship, 'tenant_id', None)
    
    return {
        'id': db_relationship.id,
        'person_id': db_relationship.person_id,
        'person_name': final_person_name,
        'related_person_id': db_relationship.related_person_id,
        'related_person_name': final_related_person_name,
        'relationship_type': db_relationship.relationship_type,
        'person_role': db_relationship.person_role,
        'related_person_role': db_relationship.related_person_role,
        'shop_id': shop_id,
        'tenant_id': tenant_id,
        'create_by': db_relationship.create_by,
        'create_time': db_relationship.create_time,
        'update_time': db_relationship.update_time,
        'status': db_relationship.status
    }


def calculate_distance(lat1: float, lon1: float, lat2: float, lon2: float) -> float:
    """
    使用Haversine公式计算地球表面两点间的距离（米）
    
    Args:
        lat1: 第一个点的纬度
        lon1: 第一个点的经度
        lat2: 第二个点的纬度
        lon2: 第二个点的经度
    
    Returns:
        距离（米）
    """
    # 地球半径（米）
    R = 6371000
    
    # 将度数转换为弧度
    lat1_rad = math.radians(lat1)
    lon1_rad = math.radians(lon1)
    lat2_rad = math.radians(lat2)
    lon2_rad = math.radians(lon2)
    
    # 计算差值
    dlat = lat2_rad - lat1_rad
    dlon = lon2_rad - lon1_rad
    
    # Haversine公式
    a = math.sin(dlat/2)**2 + math.cos(lat1_rad) * math.cos(lat2_rad) * math.sin(dlon/2)**2
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1-a))
    
    # 计算距离
    distance = R * c
    
    return distance


class FamilyTreeCRUD:
    """族谱成员CRUD操作"""
    
    @staticmethod
    def check_name_duplicate(db: Session, name: str, shop_id: str, exclude_id: str = None) -> bool:
        """
        检查同一店铺内是否存在相同姓名的成员
        
        Args:
            db: 数据库会话
            name: 姓名
            shop_id: 店铺 ID
            exclude_id: 排除的成员 ID（更新时用）
        
        Returns:
            bool: True 表示存在重复，False 表示不重复
        """
        query = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.name == name,
                FamilyTreeModel.shop_id == shop_id,
                FamilyTreeModel.status == 1
            )
        )
        
        # 更新时排除当前记录
        if exclude_id:
            query = query.filter(FamilyTreeModel.id != exclude_id)
        
        existing_member = query.first()
        return existing_member is not None
    
    @staticmethod
    def create_family_member(db: Session, member: FamilyTreeCreate) -> dict:
        """创建族谱成员"""
        # 检查同一店铺内姓名是否重复
        if member.shop_id and FamilyTreeCRUD.check_name_duplicate(db, member.name, member.shop_id):
            raise ValueError("已创建族员信息，请勿重复创建")
        
        db_member = FamilyTreeModel(**member.model_dump())
        db.add(db_member)
        db.commit()
        db.refresh(db_member)
        
        # 转换为字典以支持JSON序列化
        return family_tree_model_to_dict(db_member)
    
    @staticmethod
    def get_family_member(db: Session, member_id: str) -> Optional[dict]:
        """根据ID获取族谱成员"""
        db_member = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.id == member_id,
                FamilyTreeModel.status == 1
            )
        ).first()
        
        if not db_member:
            return None
            
        # 转换为字典以支持JSON序列化
        return family_tree_model_to_dict(db_member)
    
    @staticmethod
    def get_family_members(db: Session, query: FamilyTreeQuery) -> Tuple[List[dict], int]:
        """获取族谱成员列表（支持分页和筛选）"""
        # 构建查询条件
        conditions = [FamilyTreeModel.status == (query.status if query.status is not None else 1)]
        
        if query.name:
            conditions.append(FamilyTreeModel.name.ilike(f"%{query.name}%"))
        
        if query.generation is not None:
            conditions.append(FamilyTreeModel.generation == query.generation)
        
        if query.shop_id:
            conditions.append(FamilyTreeModel.shop_id == query.shop_id)
        
        if query.tenant_id:
            conditions.append(FamilyTreeModel.tenant_id == query.tenant_id)
        
        # 构建查询
        query_obj = db.query(FamilyTreeModel).filter(and_(*conditions))
        
        # 获取总数
        total = query_obj.count()
        
        # 分页查询
        offset = (query.page_index - 1) * query.page_size
        members = query_obj.order_by(FamilyTreeModel.create_time.desc()).offset(offset).limit(query.page_size).all()
        
        # 转换为字典列表（使用清洗版本）
        member_dicts = [family_tree_model_to_dict_clean(member) for member in members]
        
        return member_dicts, total
    
    @staticmethod
    def update_family_member(db: Session, member_id: str, member_update: FamilyTreeUpdate) -> Optional[dict]:
        """更新族谱成员信息"""
        db_member = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.id == member_id,
                FamilyTreeModel.status == 1
            )
        ).first()
        
        if not db_member:
            return None
        
        # 检查姓名是否重复（如果更新了姓名或店铺）
        update_data = member_update.model_dump(exclude_unset=True)
        if update_data:
            new_name = update_data.get('name', db_member.name)
            new_shop_id = update_data.get('shop_id', db_member.shop_id)
            
            # 如果姓名或店铺发生变化，需要检查重复
            if (new_name != db_member.name or new_shop_id != db_member.shop_id) and new_shop_id:
                if FamilyTreeCRUD.check_name_duplicate(db, new_name, new_shop_id, member_id):
                    raise ValueError("已创建族员信息，请勿重复创建")
            
            # 更新字段
            old_name = db_member.name  # 保存旧姓名用于同步
            for field, value in update_data.items():
                if hasattr(db_member, field):
                    setattr(db_member, field, value)
            
            # 如果姓名有更新，同步更新关系表中的姓名缓存
            if 'name' in update_data and update_data['name'] != old_name:
                new_name = update_data['name']
                # 更新作为 person_id 的关系记录
                db.query(FamilyRelationshipModel).filter(
                    and_(
                        FamilyRelationshipModel.person_id == member_id,
                        FamilyRelationshipModel.status == 1
                    )
                ).update({FamilyRelationshipModel.person_name: new_name})
                
                # 更新作为 related_person_id 的关系记录
                db.query(FamilyRelationshipModel).filter(
                    and_(
                        FamilyRelationshipModel.related_person_id == member_id,
                        FamilyRelationshipModel.status == 1
                    )
                ).update({FamilyRelationshipModel.related_person_name: new_name})
            
            db_member.update_time = datetime.now()
            db.commit()
            db.refresh(db_member)
        
        return family_tree_model_to_dict(db_member)
    
    @staticmethod
    def delete_family_member(db: Session, member_id: str) -> bool:
        """软删除族谱成员"""
        db_member = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.id == member_id,
                FamilyTreeModel.status == 1
            )
        ).first()
        
        if not db_member:
            return False
        
        # 软删除
        db_member.status = 0
        db_member.deleted_at = datetime.now()
        
        db.commit()
        return True
    
    @staticmethod
    def get_family_tree_by_generation(db: Session, generation: int, shop_id: str = None, tenant_id: str = None) -> List[dict]:
        """根据代数获取族谱成员（支持多租户筛选）"""
        conditions = [
            FamilyTreeModel.generation == generation,
            FamilyTreeModel.status == 1
        ]
        
        # 添加多租户筛选条件
        if shop_id:
            conditions.append(FamilyTreeModel.shop_id == shop_id)
        
        if tenant_id:
            conditions.append(FamilyTreeModel.tenant_id == tenant_id)
        
        members = db.query(FamilyTreeModel).filter(
            and_(*conditions)
        ).order_by(FamilyTreeModel.birth_date.asc()).all()
        
        return [family_tree_model_to_dict(member) for member in members]
    
    @staticmethod
    def search_family_members(db: Session, keyword: str, limit: int = 20) -> List[dict]:
        """搜索族谱成员（按姓名）"""
        members = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.name.ilike(f"%{keyword}%"),
                FamilyTreeModel.status == 1
            )
        ).limit(limit).all()
        
        return [family_tree_model_to_dict(member) for member in members]
    
    @staticmethod
    def get_nearby_graves(db: Session, query: NearbyGraveQuery) -> Tuple[List[dict], int]:
        """
        根据地理位置查找附近的墓地（分页）
        
        Args:
            db: 数据库会话
            query: 查询参数（包含经纬度、半径、分页参数等）
        
        Returns:
            Tuple[附近墓地列表, 总数量]
        """
        # 构建查询条件
        conditions = [
            FamilyTreeModel.status == 1,
            FamilyTreeModel.longitude.isnot(None),
            FamilyTreeModel.latitude.isnot(None),
            FamilyTreeModel.death_date.isnot(None)  # 只查找已故人员
        ]
        
        # 添加多租户筛选
        if query.shop_id:
            conditions.append(FamilyTreeModel.shop_id == query.shop_id)
        
        if query.tenant_id:
            conditions.append(FamilyTreeModel.tenant_id == query.tenant_id)
        
        # 查询所有有墓地坐标的成员
        members = db.query(FamilyTreeModel).filter(and_(*conditions)).all()
        
        # 计算距离并筛选在半径内的墓地
        nearby_graves = []
        for member in members:
            # 计算距离
            distance = calculate_distance(
                query.latitude, query.longitude,
                member.latitude, member.longitude
            )
            
            # 只保留在指定半径内的墓地
            if distance <= query.radius:
                # 返回简化的信息（姓名、名讳、排行字、第几代、性别、ID、坐标、地址）
                grave_info = {
                    'id': member.id,
                    'name': member.name,
                    'taboo_name': member.taboo_name,
                    'ranking_character': member.ranking_character,
                    'generation': member.generation,
                    'gender': member.gender,
                    'longitude': member.longitude,
                    'latitude': member.latitude,
                    'grave_address': member.grave_address,
                    'distance': round(distance, 2)  # 保留两位小数
                }
                nearby_graves.append(grave_info)
        
        # 按距离排序（近到远）
        nearby_graves.sort(key=lambda x: x['distance'])
        
        # 获取总数量
        total = len(nearby_graves)
        
        # 分页处理
        offset = (query.page_index - 1) * query.page_size
        paginated_graves = nearby_graves[offset:offset + query.page_size]
        
        return paginated_graves, total


class FamilyRelationshipCRUD:
    """家族关系CRUD操作"""
    
    @staticmethod
    def repair_relationship_names(db: Session) -> dict:
        """
        修复关系表中的姓名缓存字段和shop_id、tenant_id字段
        用于填充现有关系记录的缓存信息
        
        Returns:
            dict: 修复结果统计
        """
        # 检查数据库表是否支持新字段
        try:
            from sqlalchemy import inspect
            inspector = inspect(db.bind)
            columns = [col['name'] for col in inspector.get_columns('family_relationship')]
            
            has_person_name = 'person_name' in columns
            has_related_person_name = 'related_person_name' in columns
            has_shop_id = 'shop_id' in columns
            has_tenant_id = 'tenant_id' in columns
            
            # 如果没有任何新字段，返回信息
            if not any([has_person_name, has_related_person_name, has_shop_id, has_tenant_id]):
                return {
                    'total_relationships': 0,
                    'repaired_count': 0,
                    'message': '数据库表中没有新字段，无需修复'
                }
        except Exception:
            return {
                'total_relationships': 0,
                'repaired_count': 0,
                'message': '无法检查数据库表结构'
            }
        
        # 查询所有关系记录用于修复
        relationships_to_repair = db.query(FamilyRelationshipModel).filter(
            FamilyRelationshipModel.status == 1
        ).all()
        
        if not relationships_to_repair:
            return {
                'total_relationships': 0,
                'repaired_count': 0,
                'message': '没有找到关系记录'
            }
        
        # 获取所有相关人员ID
        person_ids = set()
        for rel in relationships_to_repair:
            person_ids.add(rel.person_id)
            person_ids.add(rel.related_person_id)
        
        # 批量查询人员信息
        members = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.id.in_(person_ids),
                FamilyTreeModel.status == 1
            )
        ).all()
        
        # 构建人员信息映射
        member_info = {member.id: member for member in members}
        
        # 批量更新关系记录的缓存信息
        repaired_count = 0
        for rel in relationships_to_repair:
            updates = {}
            
            # 获取person信息
            person_info = member_info.get(rel.person_id)
            
            # 安全地更新字段
            if has_person_name and person_info and (not hasattr(rel, 'person_name') or not rel.person_name):
                updates[FamilyRelationshipModel.person_name] = person_info.name
            
            if has_related_person_name and rel.related_person_id in member_info and (not hasattr(rel, 'related_person_name') or not rel.related_person_name):
                updates[FamilyRelationshipModel.related_person_name] = member_info[rel.related_person_id].name
            
            if has_shop_id and person_info and person_info.shop_id and (not hasattr(rel, 'shop_id') or not rel.shop_id):
                updates[FamilyRelationshipModel.shop_id] = person_info.shop_id
            
            if has_tenant_id and person_info and person_info.tenant_id and (not hasattr(rel, 'tenant_id') or not rel.tenant_id):
                updates[FamilyRelationshipModel.tenant_id] = person_info.tenant_id
            
            # 如果有更新，执行更新
            if updates:
                db.query(FamilyRelationshipModel).filter(
                    FamilyRelationshipModel.id == rel.id
                ).update(updates)
                repaired_count += 1
        
        # 提交所有更新
        db.commit()
        
        return {
            'total_relationships': len(relationships_to_repair),
            'repaired_count': repaired_count,
            'message': f'成功修复 {repaired_count} 条关系记录的缓存信息'
        }
    
    @staticmethod
    def check_relationship_exists(db: Session, person_id: str, related_person_id: str, 
                                relationship_type: str, person_role: str, related_person_role: str) -> bool:
        """
        检查关系是否已存在（双向检查，防止重复关系）
        
        Args:
            db: 数据库会话
            person_id: 人员 ID
            related_person_id: 关系人 ID  
            relationship_type: 关系类型
            person_role: person_id的角色
            related_person_role: related_person_id的角色
        
        Returns:
            bool: True 表示已存在，False 表示不存在
        """
        # 检查正向关系：person_id -> related_person_id
        forward_exists = db.query(FamilyRelationshipModel).filter(
            and_(
                FamilyRelationshipModel.person_id == person_id,
                FamilyRelationshipModel.related_person_id == related_person_id,
                FamilyRelationshipModel.relationship_type == relationship_type,
                FamilyRelationshipModel.person_role == person_role,
                FamilyRelationshipModel.related_person_role == related_person_role,
                FamilyRelationshipModel.status == 1
            )
        ).first()
        
        # 检查反向关系：related_person_id -> person_id (角色交换)
        reverse_exists = db.query(FamilyRelationshipModel).filter(
            and_(
                FamilyRelationshipModel.person_id == related_person_id,
                FamilyRelationshipModel.related_person_id == person_id,
                FamilyRelationshipModel.relationship_type == relationship_type,
                FamilyRelationshipModel.person_role == related_person_role,
                FamilyRelationshipModel.related_person_role == person_role,
                FamilyRelationshipModel.status == 1
            )
        ).first()
        
        return forward_exists is not None or reverse_exists is not None
    
    @staticmethod
    def batch_create_relationships(db: Session, relationships: List, create_by: str = None) -> dict:
        """
        批量创建家族关系，过滤重复关系
        
        Args:
            db: 数据库会话
            relationships: 关系列表
            create_by: 创建人 ID
        
        Returns:
            dict: 批量创建结果
        """
        created_relationships = []
        skipped_relationships = []
        
        for rel_data in relationships:
            person_id = rel_data.person_id
            related_person_id = rel_data.related_person_id
            person_role = rel_data.person_role
            related_person_role = rel_data.related_person_role
            
            try:
                # 检查角色一致性
                if not check_relationship_consistency(person_role, related_person_role):
                    skipped_relationships.append({
                        'person_id': person_id,
                        'related_person_id': related_person_id,
                        'person_role': person_role,
                        'related_person_role': related_person_role,
                        'reason': f'不支持的角色组合: {person_role} - {related_person_role}'
                    })
                    continue
                
                # 自动推导关系类型
                relationship_info = determine_relationship_info(person_role, related_person_role)
                relationship_type = relationship_info['relationship_type']
                
                # 检查关系是否已存在（双向检查）
                if FamilyRelationshipCRUD.check_relationship_exists(
                    db, person_id, related_person_id, relationship_type, person_role, related_person_role
                ):
                    skipped_relationships.append({
                        'person_id': person_id,
                        'related_person_id': related_person_id,
                        'person_role': person_role,
                        'related_person_role': related_person_role,
                        'reason': '关系已存在'
                    })
                    continue
                
                # 检查参与关系的两个成员是否存在
                person_exists = db.query(FamilyTreeModel).filter(
                    and_(
                        FamilyTreeModel.id == person_id,
                        FamilyTreeModel.status == 1
                    )
                ).first()
                
                related_person_exists = db.query(FamilyTreeModel).filter(
                    and_(
                        FamilyTreeModel.id == related_person_id,
                        FamilyTreeModel.status == 1
                    )
                ).first()
                
                if not person_exists:
                    skipped_relationships.append({
                        'person_id': person_id,
                        'related_person_id': related_person_id,
                        'person_role': person_role,
                        'related_person_role': related_person_role,
                        'reason': f'人员ID {person_id} 不存在'
                    })
                    continue
                
                if not related_person_exists:
                    skipped_relationships.append({
                        'person_id': person_id,
                        'related_person_id': related_person_id,
                        'person_role': person_role,
                        'related_person_role': related_person_role,
                        'reason': f'关系人 ID {related_person_id} 不存在'
                    })
                    continue
                
                # 创建关系基础字段
                relationship_dict = {
                    'person_id': person_id,
                    'related_person_id': related_person_id,
                    'relationship_type': relationship_type,
                    'person_role': person_role,
                    'related_person_role': related_person_role
                }
                
                # 尝试添加缓存字段（如果数据库支持）
                try:
                    from sqlalchemy import inspect
                    inspector = inspect(db.bind)
                    columns = [col['name'] for col in inspector.get_columns('family_relationship')]
                    
                    if 'person_name' in columns:
                        relationship_dict['person_name'] = person_exists.name
                    if 'related_person_name' in columns:
                        relationship_dict['related_person_name'] = related_person_exists.name
                    if 'shop_id' in columns:
                        # 优先使用请求体中的shop_id，然后才从person信息继承
                        relationship_dict['shop_id'] = (
                            getattr(rel_data, 'shop_id', None) or 
                            getattr(person_exists, 'shop_id', None)
                        )
                    if 'tenant_id' in columns:
                        # 优先使用请求体中的tenant_id，然后才从person信息继承
                        relationship_dict['tenant_id'] = (
                            getattr(rel_data, 'tenant_id', None) or 
                            getattr(person_exists, 'tenant_id', None)
                        )
                except Exception:
                    # 如果检查失败，则只使用基础字段
                    pass
                if create_by:
                    relationship_dict['create_by'] = create_by
                
                db_relationship = FamilyRelationshipModel(**relationship_dict)
                db.add(db_relationship)
                db.flush()  # 获取 ID 但不提交
                
                # 获取人员名称
                person_name = person_exists.name if person_exists else None
                related_person_name = related_person_exists.name if related_person_exists else None
                
                created_relationships.append(family_relationship_model_to_dict_with_names(
                    db_relationship, person_name, related_person_name
                ))
                
            except Exception as e:
                skipped_relationships.append({
                    'person_id': person_id,
                    'related_person_id': related_person_id,
                    'person_role': person_role,
                    'related_person_role': related_person_role,
                    'reason': f'创建失败: {str(e)}'
                })
        
        # 提交所有成功的创建
        db.commit()
        
        return {
            'total_requested': len(relationships),
            'created_count': len(created_relationships),
            'skipped_count': len(skipped_relationships),
            'created_relationships': created_relationships,
            'skipped_relationships': skipped_relationships
        }
    
    @staticmethod
    def create_relationship(db: Session, relationship: FamilyRelationshipCreate) -> dict:
        """创建家族关系"""
        person_id = relationship.person_id
        related_person_id = relationship.related_person_id
        person_role = relationship.person_role
        related_person_role = relationship.related_person_role
        
        # 检查角色一致性
        if not check_relationship_consistency(person_role, related_person_role):
            raise ValueError(f"不支持的角色组合: {person_role} - {related_person_role}")
        
        # 自动推导关系类型
        relationship_info = determine_relationship_info(person_role, related_person_role)
        relationship_type = relationship_info['relationship_type']
        
        # 检查是否已存在相同关系
        if FamilyRelationshipCRUD.check_relationship_exists(
            db, person_id, related_person_id, relationship_type, person_role, related_person_role
        ):
            # 如果已存在，返回现有关系
            existing = db.query(FamilyRelationshipModel).filter(
                and_(
                    or_(
                        and_(
                            FamilyRelationshipModel.person_id == person_id,
                            FamilyRelationshipModel.related_person_id == related_person_id,
                            FamilyRelationshipModel.person_role == person_role,
                            FamilyRelationshipModel.related_person_role == related_person_role
                        ),
                        and_(
                            FamilyRelationshipModel.person_id == related_person_id,
                            FamilyRelationshipModel.related_person_id == person_id,
                            FamilyRelationshipModel.person_role == related_person_role,
                            FamilyRelationshipModel.related_person_role == person_role
                        )
                    ),
                    FamilyRelationshipModel.relationship_type == relationship_type,
                    FamilyRelationshipModel.status == 1
                )
            ).first()
            return family_relationship_model_to_dict_with_names(
                existing, person_exists.name, related_person_exists.name
            )
        
        # 检查参与关系的两个成员是否存在
        person_exists = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.id == person_id,
                FamilyTreeModel.status == 1
            )
        ).first()
        
        related_person_exists = db.query(FamilyTreeModel).filter(
            and_(
                FamilyTreeModel.id == related_person_id,
                FamilyTreeModel.status == 1
            )
        ).first()
        
        if not person_exists:
            raise ValueError(f"人员ID {person_id} 不存在")
        
        if not related_person_exists:
            raise ValueError(f"关系人ID {related_person_id} 不存在")
        
        # 创建关系基础字段
        relationship_dict = {
            'person_id': person_id,
            'related_person_id': related_person_id,
            'relationship_type': relationship_type,
            'person_role': person_role,
            'related_person_role': related_person_role,
            'create_by': relationship.create_by
        }
        
        # 尝试添加缓存字段（如果数据库支持）
        try:
            # 检查数据库表是否支持新字段
            from sqlalchemy import inspect
            inspector = inspect(db.bind)
            columns = [col['name'] for col in inspector.get_columns('family_relationship')]
            
            if 'person_name' in columns:
                relationship_dict['person_name'] = person_exists.name
            if 'related_person_name' in columns:
                relationship_dict['related_person_name'] = related_person_exists.name
            if 'shop_id' in columns:
                # 优先使用请求体中的shop_id，然后才从person信息继承
                relationship_dict['shop_id'] = (
                    getattr(relationship, 'shop_id', None) or 
                    getattr(person_exists, 'shop_id', None)
                )
            if 'tenant_id' in columns:
                # 优先使用请求体中的tenant_id，然后才从person信息继承
                relationship_dict['tenant_id'] = (
                    getattr(relationship, 'tenant_id', None) or 
                    getattr(person_exists, 'tenant_id', None)
                )
        except Exception:
            # 如果检查失败，则只使用基础字段
            pass
        
        db_relationship = FamilyRelationshipModel(**relationship_dict)
        db.add(db_relationship)
        db.commit()
        db.refresh(db_relationship)
        
        # 获取人员名称
        person_name = person_exists.name if person_exists else None
        related_person_name = related_person_exists.name if related_person_exists else None
        
        return family_relationship_model_to_dict_with_names(
            db_relationship, person_name, related_person_name
        )
    
    @staticmethod
    def get_relationships_by_person(db: Session, person_id: str) -> List[dict]:
        """获取某人的所有关系（优先使用缓存名称，必要时查询实时名称）"""
        # 获取关系记录
        relationships = db.query(FamilyRelationshipModel).filter(
            and_(
                or_(
                    FamilyRelationshipModel.person_id == person_id,
                    FamilyRelationshipModel.related_person_id == person_id
                ),
                FamilyRelationshipModel.status == 1
            )
        ).all()
        
        # 分类需要查询名称的关系和不需要的关系
        needs_name_query = []
        person_ids_to_query = set()
        
        for rel in relationships:
            # 安全地检查是否需要查询名称
            person_name_exists = hasattr(rel, 'person_name') and rel.person_name
            related_person_name_exists = hasattr(rel, 'related_person_name') and rel.related_person_name
            
            if not person_name_exists or not related_person_name_exists:
                needs_name_query.append(rel)
                if not person_name_exists:
                    person_ids_to_query.add(rel.person_id)
                if not related_person_name_exists:
                    person_ids_to_query.add(rel.related_person_id)
        
        # 只在需要时才查询人员信息
        member_names = {}
        if person_ids_to_query:
            members = db.query(FamilyTreeModel).filter(
                and_(
                    FamilyTreeModel.id.in_(person_ids_to_query),
                    FamilyTreeModel.status == 1
                )
            ).all()
            member_names = {member.id: member.name for member in members}
        
        # 转换为字典列表，包含名称信息
        result = []
        for rel in relationships:
            # 安全地获取缓存名称
            cached_person_name = getattr(rel, 'person_name', None)
            cached_related_person_name = getattr(rel, 'related_person_name', None)
            
            person_name = cached_person_name or member_names.get(rel.person_id)
            related_person_name = cached_related_person_name or member_names.get(rel.related_person_id)
            
            result.append(family_relationship_model_to_dict_with_names(
                rel, person_name, related_person_name
            ))
        
        return result
    
    @staticmethod
    def get_relationships(db: Session, query: FamilyRelationshipQuery) -> Tuple[List[dict], int]:
        """获取关系列表（支持分页和筛选，包含名称信息）"""
        conditions = [FamilyRelationshipModel.status == 1]
        
        if query.person_id:
            conditions.append(
                or_(
                    FamilyRelationshipModel.person_id == query.person_id,
                    FamilyRelationshipModel.related_person_id == query.person_id
                )
            )
        
        if query.relationship_type:
            conditions.append(FamilyRelationshipModel.relationship_type == query.relationship_type)
        
        # 安全地添加多租户筛选条件（检查字段是否存在）
        try:
            from sqlalchemy import inspect
            inspector = inspect(db.bind)
            columns = [col['name'] for col in inspector.get_columns('family_relationship')]
            
            if query.shop_id and 'shop_id' in columns:
                conditions.append(FamilyRelationshipModel.shop_id == query.shop_id)
            
            if query.tenant_id and 'tenant_id' in columns:
                conditions.append(FamilyRelationshipModel.tenant_id == query.tenant_id)
        except Exception:
            # 如果检查失败，则忽略多租户筛选
            pass
        
        query_obj = db.query(FamilyRelationshipModel).filter(and_(*conditions))
        
        # 获取总数
        total = query_obj.count()
        
        # 分页查询
        offset = (query.page_index - 1) * query.page_size
        relationships = query_obj.order_by(FamilyRelationshipModel.create_time.desc()).offset(offset).limit(query.page_size).all()
        
        # 获取所有相关人员ID
        person_ids = set()
        for rel in relationships:
            person_ids.add(rel.person_id)
            person_ids.add(rel.related_person_id)
        
        # 批量查询人员信息
        if person_ids:
            members = db.query(FamilyTreeModel).filter(
                and_(
                    FamilyTreeModel.id.in_(person_ids),
                    FamilyTreeModel.status == 1
                )
            ).all()
            
            # 构建人员名称映射
            member_names = {member.id: member.name for member in members}
        else:
            member_names = {}
        
        # 转换为字典列表，包含名称信息
        relationship_dicts = []
        for rel in relationships:
            person_name = member_names.get(rel.person_id)
            related_person_name = member_names.get(rel.related_person_id)
            relationship_dicts.append(family_relationship_model_to_dict_with_names(
                rel, person_name, related_person_name
            ))
        
        return relationship_dicts, total
    
    @staticmethod
    def delete_relationship(db: Session, relationship_id: str) -> bool:
        """删除关系"""
        db_relationship = db.query(FamilyRelationshipModel).filter(
            and_(
                FamilyRelationshipModel.id == relationship_id,
                FamilyRelationshipModel.status == 1
            )
        ).first()
        
        if not db_relationship:
            return False
        
        db_relationship.status = 0
        db.commit()
        return True
    
    @staticmethod
    def get_family_tree_structure(db: Session, root_person_id: Optional[str] = None, shop_id: str = None, tenant_id: str = None) -> List[dict]:
        """获取族谱树形结构（支持多租户筛选，限制两层搜索深度，只查询parent_child直系父子关系）"""
        # 构建关系查询条件
        relationship_conditions = [FamilyRelationshipModel.status == 1]
        if shop_id:
            relationship_conditions.append(FamilyRelationshipModel.shop_id == shop_id)
        if tenant_id:
            relationship_conditions.append(FamilyRelationshipModel.tenant_id == tenant_id)
        
        # 构建成员查询条件
        member_conditions = [FamilyTreeModel.status == 1]
        if shop_id:
            member_conditions.append(FamilyTreeModel.shop_id == shop_id)
        if tenant_id:
            member_conditions.append(FamilyTreeModel.tenant_id == tenant_id)
        
        # 如果指定了root_person_id，从指定人员开始搜索
        if root_person_id:
            # 获取指定的根人员
            root_person = db.query(FamilyTreeModel).filter(
                and_(
                    FamilyTreeModel.id == root_person_id,
                    *member_conditions
                )
            ).first()
            
            if not root_person:
                return []
            
            return FamilyRelationshipCRUD._build_limited_tree_from_root(db, root_person, relationship_conditions, member_conditions)
        else:
            # 没有指定根人员，获取所有成员并构建限制深度的结构
            members = db.query(FamilyTreeModel).filter(
                and_(*member_conditions)
            ).all()
            
            return FamilyRelationshipCRUD._build_limited_tree_for_all_members(db, members, relationship_conditions)
    
    @staticmethod
    def _build_limited_tree_from_root(db: Session, root_person, relationship_conditions: list, member_conditions: list) -> List[dict]:
        """从指定根人员构建限制深度的族谱结构（两层）"""
        processed_members = set()
        family_tree = []
        
        # 第一层：根人员的直接关系
        root_info = FamilyRelationshipCRUD._get_member_with_direct_relationships(db, root_person, relationship_conditions, member_conditions)
        processed_members.add(root_person.id)
        family_tree.append(root_info)
        
        # 第二层：直接关系人员的直接关系
        for relationship in root_info["relationships"]:
            related_person_id = relationship["related_person_id"]
            
            if related_person_id not in processed_members:
                # 获取关系人员信息
                related_person = db.query(FamilyTreeModel).filter(
                    and_(
                        FamilyTreeModel.id == related_person_id,
                        *member_conditions
                    )
                ).first()
                
                if related_person:
                    related_info = FamilyRelationshipCRUD._get_member_with_direct_relationships(db, related_person, relationship_conditions, member_conditions)
                    processed_members.add(related_person_id)
                    family_tree.append(related_info)
        
        return family_tree
    
    @staticmethod
    def _build_limited_tree_for_all_members(db: Session, members: list, relationship_conditions: list) -> List[dict]:
        """为所有成员构建限制深度的族谱结构（只显示直接关系）"""
        family_tree = []
        member_dict = {member.id: member for member in members}
        
        # 获取所有关系（只查询parent_child关系）
        relationships = db.query(FamilyRelationshipModel).filter(
            and_(
                FamilyRelationshipModel.relationship_type == "parent_child",  # 只查询parent_child关系
                *relationship_conditions
            )
        ).all()
        
        # 为每个成员构建信息（只包含直接关系）
        for member in members:
            # 获取该成员的所有直接关系（只查询parent_child关系）
            member_relationships = [
                rel for rel in relationships 
                if (rel.person_id == member.id or rel.related_person_id == member.id) and rel.relationship_type == "parent_child"
            ]
            
            member_info = {
                "id": member.id,
                "name": member.name,
                "birth_date": member.birth_date,
                "gender": member.gender,
                "generation": member.generation,
                "avatar": member.avatar,
                "relationships": []
            }
            
            # 只添加直接关系信息
            for rel in member_relationships:
                related_id = rel.related_person_id if rel.person_id == member.id else rel.person_id
                related_member = member_dict.get(related_id)
                
                if related_member:
                    relationship_info = {
                        "related_person_id": related_id,
                        "related_person_name": related_member.name,
                        "relationship_type": rel.relationship_type,
                        "is_main_relation": rel.person_id == member.id
                    }
                    member_info["relationships"].append(relationship_info)
            
            family_tree.append(member_info)
        
        return family_tree
    
    @staticmethod
    def _get_member_with_direct_relationships(db: Session, member, relationship_conditions: list, member_conditions: list) -> dict:
        """获取成员信息及其直接关系"""
        # 获取该成员的所有直接关系（只查询parent_child关系）
        member_relationships = db.query(FamilyRelationshipModel).filter(
            and_(
                or_(
                    FamilyRelationshipModel.person_id == member.id,
                    FamilyRelationshipModel.related_person_id == member.id
                ),
                FamilyRelationshipModel.relationship_type == "parent_child",  # 只查询parent_child关系
                *relationship_conditions
            )
        ).all()
        
        member_info = {
            "id": member.id,
            "name": member.name,
            "birth_date": member.birth_date,
            "gender": member.gender,
            "generation": member.generation,
            "avatar": member.avatar,
            "relationships": []
        }
        
        # 获取相关人员ID列表
        related_person_ids = set()
        for rel in member_relationships:
            related_id = rel.related_person_id if rel.person_id == member.id else rel.person_id
            related_person_ids.add(related_id)
        
        # 批量查询相关人员信息
        related_members = {}
        if related_person_ids:
            members = db.query(FamilyTreeModel).filter(
                and_(
                    FamilyTreeModel.id.in_(related_person_ids),
                    *member_conditions
                )
            ).all()
            related_members = {member.id: member for member in members}
        
        # 添加关系信息
        for rel in member_relationships:
            related_id = rel.related_person_id if rel.person_id == member.id else rel.person_id
            related_member = related_members.get(related_id)
            
            if related_member:
                relationship_info = {
                    "related_person_id": related_id,
                    "related_person_name": related_member.name,
                    "relationship_type": rel.relationship_type,
                    "is_main_relation": rel.person_id == member.id
                }
                member_info["relationships"].append(relationship_info)
        
        return member_info
    
    @staticmethod
    def get_descendants(db: Session, person_id: str, max_generations: int = 5, shop_id: str = None, tenant_id: str = None) -> List[dict]:
        """获取某人的后代（递归查询，只查询parent_child直系父子关系，支持多租户筛选）"""
        descendants = []
        found_ids = set()  # 用于去重
        
        def get_children(parent_id: str, current_generation: int):
            if current_generation > max_generations:
                return
            
            # 构建基础查询条件
            base_conditions = [
                FamilyRelationshipModel.status == 1
            ]
            
            # 添加多租户筛选条件（只在有值时才添加）
            if shop_id:
                base_conditions.append(FamilyRelationshipModel.shop_id == shop_id)
            if tenant_id:
                base_conditions.append(FamilyRelationshipModel.tenant_id == tenant_id)
            
            # 优先查找直系子女关系 (parent_child) - 优先级最高
            # 情况1: parent_id作为person_id，person_role为father/mother
            children_conditions_1 = base_conditions + [
                FamilyRelationshipModel.person_id == parent_id,
                FamilyRelationshipModel.relationship_type == "parent_child",
                FamilyRelationshipModel.person_role.in_(["father", "mother"])
            ]
            children_relations_1 = db.query(FamilyRelationshipModel).filter(
                and_(*children_conditions_1)
            ).all()
            
            # 情况2: parent_id作为related_person_id，related_person_role为father/mother
            children_conditions_2 = base_conditions + [
                FamilyRelationshipModel.related_person_id == parent_id,
                FamilyRelationshipModel.relationship_type == "parent_child",
                FamilyRelationshipModel.related_person_role.in_(["father", "mother"])
            ]
            children_relations_2 = db.query(FamilyRelationshipModel).filter(
                and_(*children_conditions_2)
            ).all()
            
            # 先处理直系父子关系（优先级高）
            parent_child_relations = children_relations_1 + children_relations_2
            
            for rel in parent_child_relations:
                # 确定子女ID的正确逻辑
                if rel.person_id == parent_id:
                    # parent_id作为person_id（父母），则related_person_id是子女
                    child_id = rel.related_person_id
                else:
                    # parent_id作为related_person_id（父母），则person_id是子女
                    child_id = rel.person_id
                
                # 去重检查
                if child_id in found_ids:
                    continue
                
                # 查询子女信息
                child = db.query(FamilyTreeModel).filter(
                    and_(
                        FamilyTreeModel.id == child_id,
                        FamilyTreeModel.status == 1
                    )
                ).first()
                
                if child:
                    # 添加到已找到的ID集合中
                    found_ids.add(child_id)
                    
                    child_info = {
                        "id": child.id,
                        "name": child.name,
                        "birth_date": child.birth_date,
                        "gender": child.gender,
                        "generation": child.generation,
                        "relationship_type": rel.relationship_type,
                        "generation_level": current_generation,
                        "parentId": parent_id,  # 添加父母ID字段
                        "children": []
                    }
                    
                    # 递归获取下一代
                    get_children(child.id, current_generation + 1)
                    descendants.append(child_info)
            




        
        get_children(person_id, 1)
        return descendants
    
    @staticmethod
    def get_ancestors(db: Session, person_id: str, max_generations: int = 5, shop_id: str = None, tenant_id: str = None) -> List[dict]:
        """获取某人的祖先（递归查询，只查询parent_child直系父子关系，支持多租户筛选）"""
        ancestors = []
        found_ids = set()  # 用于去重
        
        def get_parents(child_id: str, current_generation: int):
            if current_generation > max_generations:
                return
            
            # 构建基础查询条件
            base_conditions = [
                FamilyRelationshipModel.status == 1
            ]
            
            # 添加多租户筛选条件（只在有值时才添加）
            if shop_id:
                base_conditions.append(FamilyRelationshipModel.shop_id == shop_id)
            if tenant_id:
                base_conditions.append(FamilyRelationshipModel.tenant_id == tenant_id)
            
            # 优先查找直系父母关系 (parent_child) - 优先级最高
            # 情况1: child_id作为related_person_id，related_person_role为son/daughter
            parent_conditions_1 = base_conditions + [
                FamilyRelationshipModel.related_person_id == child_id,
                FamilyRelationshipModel.relationship_type == "parent_child",
                FamilyRelationshipModel.related_person_role.in_(["son", "daughter"])
            ]
            parent_relations_1 = db.query(FamilyRelationshipModel).filter(
                and_(*parent_conditions_1)
            ).all()
            
            # 情况2: child_id作为person_id，person_role为son/daughter
            parent_conditions_2 = base_conditions + [
                FamilyRelationshipModel.person_id == child_id,
                FamilyRelationshipModel.relationship_type == "parent_child",
                FamilyRelationshipModel.person_role.in_(["son", "daughter"])
            ]
            parent_relations_2 = db.query(FamilyRelationshipModel).filter(
                and_(*parent_conditions_2)
            ).all()
            
            # 先处理直系父子关系（优先级高）
            parent_child_relations = parent_relations_1 + parent_relations_2
            
            for rel in parent_child_relations:
                # 确定父母ID的正确逻辑
                if rel.related_person_id == child_id:
                    # child_id作为related_person_id（子女），则person_id是父母
                    parent_id = rel.person_id
                else:
                    # child_id作为person_id（子女），则related_person_id是父母
                    parent_id = rel.related_person_id
                
                # 去重检查
                if parent_id in found_ids:
                    continue
                
                # 查询父母信息
                parent = db.query(FamilyTreeModel).filter(
                    and_(
                        FamilyTreeModel.id == parent_id,
                        FamilyTreeModel.status == 1
                    )
                ).first()
                
                if parent:
                    # 添加到已找到的ID集合中
                    found_ids.add(parent_id)
                    
                    # 查询当前父母的父母ID（父母的父母）
                    parent_of_parent_id = None
                    # 构建父母的父母查询条件
                    parent_of_parent_conditions = base_conditions + [
                        or_(
                            and_(
                                FamilyRelationshipModel.related_person_id == parent_id,
                                FamilyRelationshipModel.relationship_type == "parent_child",
                                FamilyRelationshipModel.related_person_role.in_(["son", "daughter"])
                            ),
                            and_(
                                FamilyRelationshipModel.person_id == parent_id,
                                FamilyRelationshipModel.relationship_type == "parent_child",
                                FamilyRelationshipModel.person_role.in_(["son", "daughter"])
                            )
                        )
                    ]
                    parent_of_parent_relations = db.query(FamilyRelationshipModel).filter(
                        and_(*parent_of_parent_conditions)
                    ).first()
                    
                    if parent_of_parent_relations:
                        if parent_of_parent_relations.related_person_id == parent_id:
                            parent_of_parent_id = parent_of_parent_relations.person_id
                        else:
                            parent_of_parent_id = parent_of_parent_relations.related_person_id
                    
                    parent_info = {
                        "id": parent.id,
                        "name": parent.name,
                        "birth_date": parent.birth_date,
                        "gender": parent.gender,
                        "generation": parent.generation,
                        "relationship_type": rel.relationship_type,
                        "generation_level": current_generation,
                        "parentId": parent_of_parent_id,  # 父母的父母ID
                        "childId": child_id  # 子女ID
                    }
                    
                    # 递归获取上一代
                    get_parents(parent.id, current_generation + 1)
                    ancestors.append(parent_info)




        
        get_parents(person_id, 1)
        return ancestors